/**
 * 
 */
package com.gkwave.module.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.gkwave.module.bean.RouteBean;
import com.gkwave.util.ListUtil;

/**
 * @author Administrator
 * 
 */
public class TransportSearchDAO extends BaseDAO {
	private Statement stmt = null;
	private ResultSet rset = null;
	private Connection con = null;

	/**
	 * 
	 */
	public TransportSearchDAO() {
		// TODO Auto-generated constructor stub
	}

	public RouteBean getTransportSearchList(Integer id_route) {
		// TODO Auto-generated method stub
		try {
			return getRouteByRouteID(id_route);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public RouteBean getRouteByRouteID(int idRoute) throws Exception {
		// TODO Auto-generated method stub
		String query = "SELECT t1.ts_up_trip,t1.id_location,t2.nm_boarding_location FROM transport_route t1,boarding_location t2 where t1.id_route="
				+ idRoute
				+ " and t1.id_location=t2.id_boarding_location order by t1.nr_display_order";
		System.out.println("Query: " + query);
		BaseDAO dao = null;
		RouteBean xbean = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			RouteBean bean = null;

			if (!rset.wasNull()) {
				System.out.println("result is not null");
				xbean = new RouteBean();
			}
			while (rset.next()) {
				Integer idLocation = rset.getInt("t1.id_location");
				String tsUpTrip = rset.getString("t1.ts_up_trip");
				String nmBoardingLocation = rset
						.getString("t2.nm_boarding_location");

				bean = new RouteBean(idLocation, tsUpTrip, nmBoardingLocation);
				xbean.entries.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception(e);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			}
		}
		return xbean;
	}

	public static void main(String[] arg) {
		try {
			TransportSearchDAO o = new TransportSearchDAO();
			// System.out.println(new
			// RouteBean().marsel(o.getTransportSearchList(4)));
			RouteBean bean = o.getTransportSearchList(1,21);
			System.out.println(new RouteBean().marsel(bean));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public RouteBean getTransportSearchList(Integer id_location, String tsTime) {
		// TODO Auto-generated method stub
/*		String query = "SELECT t2.nm_route,t1.ts_up_trip,t3.nm_boarding_location "
				+ "FROM transport_route t1,m_route t2,m_boarding_location t3 "
				+ "where t1.id_location="
				+ id_location
				+ " and t1.id_location=t3.id_boarding_location "
				+ "and t1.ts_up_trip<='"
				+ tsTime
				+ "' and t1.id_route=t2.id_route";

		System.out.println("Query: " + query);
		BaseDAO dao = null;
		RouteBean xbean = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			RouteBean bean = null;

			if (!rset.wasNull()) {
				System.out.println("result-set is not null");
				xbean = new RouteBean();
			}
			while (rset.next()) {
				String nmRoute = rset.getString("t2.nm_route");
				String tsUpTrip = rset.getString("t1.ts_up_trip");
				String nmBoardingLocation = rset
						.getString("t3.nm_boarding_location");

				bean = new RouteBean(nmRoute, tsUpTrip, nmBoardingLocation);
				xbean.entries.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}*/
		return getRouteList(id_location, tsTime);
		
	}

	private RouteBean getRouteList(Integer id_location, String tsTime){
		String query = "SELECT t1.id_route "
			+ "FROM transport_route t1 "
			+ "where t1.id_location="
			+ id_location
			+ " and t1.ts_up_trip ='"
			+ tsTime+ "'";
		List<Integer> list = new ArrayList<Integer>();
	System.out.println("Query: " + query);
	BaseDAO dao = null;
	try {
		dao = new BaseDAO();
		con = dao.openConnection();
		stmt = con.createStatement();
		rset = stmt.executeQuery(query);
		RouteBean bean = null;

		while (rset.next()) {
			Integer idRoute = rset.getInt("t1.id_route");
			list.add(idRoute);
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} finally {
		try {
			dao.closeConnection(stmt, con, rset);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		if(list.size()!=0){
			Iterator itte = list.iterator();
			// bug
			if(itte.hasNext()){
				try {
					return getRouteByRouteID((Integer)itte.next());
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			}
		}
		return null;
	}
	public RouteBean getTransportSearchList(Integer id_boarding_locationId,
			Integer id_end_locationId) {
		// TODO Auto-generated method stub
		List<Integer> aList = getRouteList(id_boarding_locationId);
		List<Integer> bList = getRouteList(id_end_locationId);
		aList = ListUtil.compareList(aList, bList);
		RouteBean xbean = new RouteBean();
		Integer size = aList.size();
		if (size != 0) {
			int count = 0;
			String query = "SELECT t3.nm_route, t2.nm_boarding_location, t1.ts_up_trip, " +
						   "t1.ts_down_trip " +
						   "FROM transport_route t1,boarding_location t2,m_route t3 " +
						   "where t1.id_route IN (?) and t1.id_location IN ("+id_boarding_locationId+","+id_end_locationId+") and t1.id_location=t2.id_boarding_location " +
						   "and t1.id_route=t3.id_route";
			
			Iterator<Integer> itte = aList.iterator();
			StringBuffer sbuffer = new StringBuffer();
			while (itte.hasNext()) {
				count++;
				System.out.println(count);
				if (count <= size && count != 1)
					sbuffer.append(",");
				sbuffer.append(itte.next());
			}
			query = query.replace("?", sbuffer.toString());
			System.out.println(query);

			BaseDAO dao = null;
			
			try {
				dao = new BaseDAO();
				con = dao.openConnection();
				stmt = con.createStatement();
				rset = stmt.executeQuery(query);
				RouteBean bean = null;

				if (!rset.wasNull()) {
					System.out.println("result-set is not null");
					xbean = new RouteBean();
				}
				while (rset.next()) {
					String nmRoute = rset.getString("t3.nm_route");
					String nmBoardingLocation = rset.getString("t2.nm_boarding_location");
					String tsUpTrip = rset.getString("t1.ts_up_trip");
					String tsDownTrip = rset.getString("t1.ts_down_trip");
					bean = new RouteBean(nmRoute, tsUpTrip, nmBoardingLocation,tsDownTrip);
					xbean.entries.add(bean);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				try {
					dao.closeConnection(stmt, con, rset);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return xbean;
	}

	private List getRouteList(Integer idLocation) {
		String query = "SELECT id_route FROM transport_route where id_location='" + idLocation + "'";

		System.out.println("Query: " + query);
		BaseDAO dao = null;
		List<Integer> aList = null;
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			aList = new ArrayList<Integer>();
			while (rset.next()) {
				Integer idRoute = rset.getInt("id_route");
				aList.add(idRoute);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return aList;
	}

	public RouteBean getTransportLMSSearchList(Integer id_boarding_locationId,Integer id_end_locationId) {
		// TODO Auto-generated method stub
		String query = "SELECT t2.nm_boarding_location, " +
							  "t2.nm_boarding_location as end_point, " +
							  "t4.nm_transport_service, " +
							  "t1.ts_pick_up, " +
							  "t1.ts_drop " +
					   "FROM transport_lms_mmts t1,boarding_location t2,m_transport_service t4 " +
					   "WHERE t1.id_boarding_location=t2.id_boarding_location and " +
					   "t1.id_transport_service=t4.id_transport_service and t1.id_boarding_location='" +id_boarding_locationId+"' and id_end_location='" +id_end_locationId+"'";
		System.out.println(query);
		RouteBean xbean = new RouteBean();
		BaseDAO dao = null;
		
		try {
			dao = new BaseDAO();
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			RouteBean bean = null;

			if (!rset.wasNull()) {
				System.out.println("result-set is not null");
				xbean = new RouteBean();
			}
			while (rset.next()) {
				String nmTransportService = rset.getString("t4.nm_transport_service");
				String nmBoardingLocation = rset.getString("t2.nm_boarding_location");
				String endPoint = rset.getString("end_point");
				String tsUpTrip = rset.getString("t1.ts_pick_up");
				String tsDownTrip = rset.getString("t1.ts_drop");
				bean = new RouteBean(nmTransportService, nmBoardingLocation, endPoint, tsUpTrip,tsDownTrip);
				xbean.entries.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	return xbean;
	}
}
